**************************************************************************************************************** fragment
**************************************** AGGREGATE DATA DOFILE
******************************************************************************************************************************************* fragment
clear all
set mem 3g 
set more off 
set matsize 11000
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
*********************************************
use "C:\PlanChoice\multic_pol_uic.dta"
sum 
describe, fullnames
drop if plan_family!="EP1" & plan_family!="HMO" & plan_family!="POS" & plan_family!="PPO"
************************************************ 
* generate date variable 
gen year  = substr( year_mo ,1,4)
destring year, replace
gen month = substr( year_mo ,5,2)
destring month, replace
gen data_date  = ym(year, month )
format data_date %tm
********************
drop if year!=2015
drop if month!=1
keep customer_number polnbr year
gen insample2015=1
*collapse (mean) insample2015, by(customer_number)
sort customer_number polnbr
tempfile temp2015
save `temp2015'

**************************************************************************************************************** fragment
**************************************************************************************************************** fragment
clear all 
insheet using "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles\2015_PRIME_csv.csv", comma
* generate date variable
tostring yearmonth, replace
gen year  = substr( yearmonth ,1,4)
destring year, replace
gen month = substr( yearmonth ,5,2)
destring month, replace
gen data_date  = ym(year, month )
format data_date %tm
drop if month!=1
rename policynumber polnbr
sort customer_number polnbr
merge customer_number polnbr using `temp2015'
drop if _merge!=3
g premium2015fixed = regexr(totalpremium, "," , "")
destring premium2015fixed, replace
keep customer_number polnbr data_date noofelig premium2015fixed
sort customer_number polnbr data_date
save "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles\aux_2015.dta", replace
exit
